package org.kl.bf.repository.basic;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.servlet.ServletRequest;

import org.apache.commons.lang3.StringUtils;
import org.kl.bf.entity.basic.User;
import org.kl.auditsupervision.entity.fnd.Employee;
import org.kl.auditsupervision.repository.fnd.EmployeeDao;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowCallbackHandler;
import org.springframework.stereotype.Component;

@Component
public class RoleUserDao {

	@Autowired
	private JdbcTemplate jdbcTemp;
	@Autowired
	private EmployeeDao employeeDao;

	/**
	 * 给角色分配用户
	 * 
	 * @param roleId
	 * @param userId
	 */
	public void addRoleUser(String roleId, String userId) {
		String sqlCount = "select count(1) from basic_user_role u where u.role_id=? and u.user_id=?";
		Integer count = jdbcTemp.queryForObject(sqlCount, Integer.class, roleId, userId);
		if (count == null || count == 0) {
			String sql = "insert into basic_user_role(user_id, role_id) values(?, ?)";
			jdbcTemp.update(sql, userId, roleId);
		}
	}

	/**
	 * 删除某个角色下某个用户
	 * 
	 * @param roleId
	 * @param userId
	 */
	public void deleteUserForRole(String roleId, String userId) {
		String sql = "delete from basic_user_role ur where ur.role_id = ? and ur.user_id = ?";
		jdbcTemp.update(sql, roleId, userId);
	}

	/**
	 * 获取用户角色下用户id
	 * 
	 * @param roleId
	 * @return
	 */
	public List<String> getUserIds(String roleId) {
		final List<String> userIds = new ArrayList<String>();
		String sql = "select u.user_id userId from basic_user_role u where u.role_id='" + roleId + "'";

		this.jdbcTemp.query(sql, new RowCallbackHandler() {
			@Override
			public void processRow(ResultSet rs) throws SQLException {
				String userId = new String();
				userId = rs.getString("userId");
				userIds.add(userId);
			}
		});
		return userIds;
	}

	/**
	 * 获取角色下的所有用户
	 * 
	 * @param roleId
	 * @return
	 */
	public List<User> getUsersByRoleId(String roleId) {
		String sql = "select u.* from basic_user u,basic_user_role ur where ur.user_id = u.id and ur.role_id='" + roleId + "'";
		final List<User> users = new ArrayList<User>();
		this.jdbcTemp.query(sql, new RowCallbackHandler() {
			@Override
			public void processRow(ResultSet rs) throws SQLException {
				User u = new User();
				u.setId(rs.getString("id"));
				u.setLoginNo(rs.getString("login_no"));
				u.setSysManager(rs.getInt("sys_manager"));
				u.setUserType(rs.getString("user_type"));
				u.setTrueName(rs.getString("true_name"));
				String employeeId = rs.getString("emp_id");
				if (StringUtils.isNotBlank(employeeId)) {
					Employee employee = employeeDao.findOne(employeeId);
					u.setEmployee(employee);
				}
				users.add(u);
			}
		});
		return users;
	}

	/**
	 * 获取角色下的所有用户并按部门过滤
	 * 
	 * @param roleId
	 * @return
	 */
	public List<User> getUsersByRoleIdAndOrgIds(String roleId, String OrgIds, int pageNumber, int pageSize,ServletRequest request) {
		String trueName = request!=null?request.getParameter("search_LIKE_trueName"):null;
		String loginNo = request!=null?request.getParameter("search_LIKE_loginNo"):null;
		String orgName = request!=null?request.getParameter("search_LIKE_employee.org.orgName"):null;
		String sql = "select u.* from basic_user u,basic_user_role ur,fnd_org o,fnd_employee s where ur.user_id = u.id and ur.role_id='"
				+ roleId + "' and u.emp_id=s.id and s.org_id= o.id and o.id in ('" + OrgIds + "')";
		if (trueName != null) {
			sql = sql + " and u.true_name like '%" + trueName + "%'";
		}
		if (loginNo != null) {
			sql = sql + " and u.login_no like '%" + loginNo  + "%'";
		}
		if (orgName != null) {
			sql = sql + " and o.org_name like '%" + orgName + "%'";
		}
		if(pageSize!=0){
			sql=getPaginationSql(sql, pageNumber, pageSize);
		}
		final List<User> users = new ArrayList<User>();
		this.jdbcTemp.query(sql, new RowCallbackHandler() {
			@Override
			public void processRow(ResultSet rs) throws SQLException {
				User u = new User();
				u.setId(rs.getString("id"));
				u.setLoginNo(rs.getString("login_no"));
				u.setSysManager(rs.getInt("sys_manager"));
				u.setUserType(rs.getString("user_type"));
				u.setTrueName(rs.getString("true_name"));
				String staffinfoId = rs.getString("emp_id");
				if (StringUtils.isNotBlank(staffinfoId)) {
					Employee employee = employeeDao.findOne(staffinfoId);
					u.setEmployee(employee);
				}
				users.add(u);
			}
		});
		return users;
	}
	
	private String getPaginationSql(String sql, int pageNumber, int pageSize) {
		String startRow = String.valueOf(pageSize * (pageNumber - 1));
		String endRow = String.valueOf(pageSize * pageNumber);
		String paginationSql = "";
		paginationSql = "select * from (select row_.*, rownum rownum_ from ("
				+ sql + ")row_ where rownum <= " + endRow + ")where rownum_ > "
				+ startRow;
		return paginationSql;
	}
	
	public int getUsersByRoleIdAndOrgIdsCount(String roleId, String OrgIds,ServletRequest request) {
		String trueName = request.getParameter("search_LIKE_trueName");
		String loginNo = request.getParameter("search_LIKE_loginNo");
		String orgName = request.getParameter("search_LIKE_employee.org.orgName");
		String sql = "select u.* from basic_user u,basic_user_role ur,fnd_org o,fnd_employee s where ur.user_id = u.id and ur.role_id='"
				+ roleId + "' and u.emp_id=s.id and s.org_id= o.id and o.id in ('" + OrgIds + "')";
		if (trueName != null) {
			sql = sql + " and u.true_name like '%" + trueName + "%'";
		}
		if (loginNo != null) {
			sql = sql + " and u.login_no like '%" + loginNo  + "%'";
		}
		if (orgName != null) {
			sql = sql + " and o.org_name like '%" + orgName + "%'";
		}

		sql="select count(1) as count from ("+sql+")";
		
		Integer count=(Integer)this.jdbcTemp.queryForObject(sql,Integer.class);
		
		int coungInt=count.intValue();

		return coungInt;
	}
}
